class PlanSqlMap(object):
    MY_PLAN = "SELECT t1.plan_id,t1.title,t2.type_name,t3.type_name AS cycle_name,t4.status_name,t5.dept_name,t6.real_name,t1.create_time FROM aoa_plan_list AS t1 LEFT JOIN aoa_type_list AS t2 ON t1.type_id=t2.type_id LEFT JOIN aoa_type_list AS t3 ON t1.cycle_id=t3.type_id LEFT JOIN aoa_status_list AS t4 ON t1.status_id=t4.status_id LEFT JOIN aoa_dept AS t5 ON t1.dept_id=t5.dept_id LEFT JOIN aoa_user AS t6 ON t1.plan_user_id=t6.user_id WHERE (t1.plan_user_id={user_id} OR t1.create_user_id={user_id})"

    INSERT_PLAN = "INSERT INTO aoa_plan_list(menu, create_time,start_time,end_time,plan_content,title,status_id,cycle_id,type_id,create_user_id,plan_user_id,parent_id,dept_id,project_id,attachments) VALUES('{menu}', now(),'{start_time}','{end_time}','{plan_content}','{title}',{status_id},{cycle_id},{type_id},{user_id},{plan_user_id},{parent_id},{dept_id},{project_id},'{attachments}')"
    INSERT_PLAN_NO_ATTACH = "INSERT INTO aoa_plan_list(menu, create_time,start_time,end_time,plan_content,title,status_id,cycle_id,type_id,create_user_id,plan_user_id,parent_id,dept_id,project_id) VALUES('{menu}', now(),'{start_time}','{end_time}','{plan_content}','{title}',{status_id},{cycle_id},{type_id},{user_id},{plan_user_id},{parent_id},{dept_id},{project_id})"
    GET_TYPE_NAME = "SELECT type_id,type_name FROM aoa_type_list WHERE type_id={type_id}"
    GET_TYPE_ID = "SELECT type_id,type_name FROM aoa_type_list WHERE type_name='{type_name}'"

    GET_SORT_VALUE = "SELECT type_id as cycle_id,sort_value FROM aoa_type_list WHERE type_id={cycle_id}"
    GET_PARENT_TYPE = "SELECT type_id as cycle_id FROM aoa_type_list WHERE type_model='aoa_plan_list' AND sort_value={sort}"
    GET_PLAN_TYPE = "SELECT type_id as cycle_id,sort_value,type_name FROM aoa_type_list WHERE type_model='aoa_plan_list'"
    GET_PLAN_BY_CYCLE = "SELECT title,plan_id FROM aoa_plan_list WHERE cycle_id={cycle_id}"
    GET_DOWN_PLAN_LIST = "SELECT t1.title,t1.plan_id,t2.type_name,t3.status_name,t4.real_name FROM aoa_plan_list as t1 inner join aoa_type_list as t2 on t1.cycle_id=t2.type_id inner join aoa_status_list as t3 on t1.status_id=t3.status_id inner join aoa_user as t4 on t1.plan_user_id=t4.user_id WHERE cycle_id={cycle_id}"

    EDIT_PLAN = "UPDATE aoa_plan_list SET plan_id=plan_id,"
    DEL_PLAN = "DELETE FROM aoa_plan_list WHERE plan_id={plan_id}"
    CHECK_PLAN = "SELECT t1.cycle_id,t1.attachments,t1.end_time,t1.start_time,t1.create_time,t1.plan_id,t1.title,t2.type_name,t3.type_name AS cycle_name,t4.status_name,t5.dept_name,t6.real_name FROM aoa_plan_list AS t1 LEFT JOIN aoa_type_list AS t2 ON t1.type_id=t2.type_id LEFT JOIN aoa_type_list AS t3 ON t1.cycle_id=t3.type_id LEFT JOIN aoa_status_list AS t4 ON t1.status_id=t4.status_id LEFT JOIN aoa_dept AS t5 ON t1.dept_id=t5.dept_id LEFT JOIN aoa_user AS t6 ON t1.plan_user_id=t6.user_id WHERE t1.plan_id={plan_id}"
    GET_ATTACH = "SELECT attachment_name,attachment_id FROM aoa_attachment_list WHERE attachment_id={attach_id}"


    GET_ID = "select @@IDENTITY as id"

    FIND_ROLE_BY_ID = "SELECT aoa_role_.role_id,aoa_role_.role_value from aoa_user inner join aoa_role_ on aoa_user.role_id = aoa_role_.role_id where user_id={user_id}"
    subordinates_id_list_all = "SELECT u.user_id from aoa_user u where u.user_id<>{user_id}"
    subordinates_id_list = "select t1.user_id from aoa_user as t1 inner join aoa_dept as t3 on t3.dept_id=t1.dept_id inner join aoa_role_ as t4 on t4.role_id=t1.role_id where t4.role_value>(select role_value from aoa_user inner join aoa_role_ on aoa_user.role_id=aoa_role_.role_id where user_id={user_id}) and t1.dept_id=(select dept_id from aoa_user where user_id={user_id})"

    GET_PLAN_BY_TYPE = "SELECT t1.plan_id,t1.title,t2.type_name,t3.type_name AS cycle_name,t4.status_name,t5.dept_name,t6.real_name,t1.create_time FROM aoa_plan_list AS t1 LEFT JOIN aoa_type_list AS t2 ON t1.type_id=t2.type_id LEFT JOIN aoa_type_list AS t3 ON t1.cycle_id=t3.type_id LEFT JOIN aoa_status_list AS t4 ON t1.status_id=t4.status_id LEFT JOIN aoa_dept AS t5 ON t1.dept_id=t5.dept_id LEFT JOIN aoa_user AS t6 ON t1.plan_user_id=t6.user_id WHERE t1.type_id={type_id}"

    ALL_PLAN = "SELECT t1.plan_id,t1.title,t2.type_name,t3.type_name AS cycle_name,t4.status_name,t5.dept_name,t6.real_name,t1.create_time FROM aoa_plan_list AS t1 LEFT JOIN aoa_type_list AS t2 ON t1.type_id=t2.type_id LEFT JOIN aoa_type_list AS t3 ON t1.cycle_id=t3.type_id LEFT JOIN aoa_status_list AS t4 ON t1.status_id=t4.status_id LEFT JOIN aoa_dept AS t5 ON t1.dept_id=t5.dept_id LEFT JOIN aoa_user AS t6 ON t1.plan_user_id=t6.user_id"


    FILTER_PARAMS = {
        "status_id": "t1.status_id={status_id}",
        "cycle_id": "t1.cycle_id={cycle_id}",
        "type_id": "t1.type_id={type_id}",
        "dept_id": "t1.dept_id={dept_id}",
        "query_str": "t1.title like '%{query_str}%'"
    }